import os,function,shutil
import mysql.connector
from datetime import datetime


mydb = mysql.connector.connect(
  host="192.168.10.81",
  user="root",
  passwd="",
  database="xiaocheng"
)
# 获取最大id
mycursor = mydb.cursor()
mycursor.execute("select max(id) from data_pingpu") 
myresult = mycursor.fetchone()
begin_id=myresult[0]+1

pic_path = 'D:/Desk/系统图库/平铺集/'
pic_paths = function.get_file_paths(pic_path)
# csv_path = 'D:/Desk/repeat.csv'
# repeat_paths=function.read_csv_file(csv_path)


db_paths = []
mycursor.execute("select path from data_pingpu")
myresult = mycursor.fetchall()
for x in myresult:
    db_paths.append(x[0])

now = datetime.now()
formatted_date = now.strftime("%Y-%m-%d")

print(len(pic_paths),len(db_paths))
datas=[]
i=0
for pic_path in pic_paths:
    file_name=os.path.basename(pic_path) 
    if pic_path not in db_paths:
        name='PP' +str(begin_id + 1000 + i)
        pic=name+os.path.splitext(file_name)[1]
        style_name=pic_path.split('/')[4]
        sort=''
        if '正' in pic_path.split('/')[5]:
            sort='正面'
        if '背' in pic_path.split('/')[5]:
            sort='背面'
        
        dict=[] 
        
        new_path="Y:/pic/pingpu/"+pic
        shutil.copy(pic_path,new_path)     
        dict.append(name)
        dict.append(pic)
        dict.append(pic_path)
        dict.append(sort)
        dict.append(style_name)
        dict.append(formatted_date)  
        datas.append(tuple(dict))
        print(tuple(dict))
        i+=1
print(datas)

# 插入数据
sql = "insert into data_pingpu (name,pic,path,sort,style_name,date) values (%s,%s,%s,%s,%s,%s)" 
mycursor.executemany(sql, datas) 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

mycursor.close()
mydb.close()

for path in db_paths:
    if path not in pic_paths:
        sql = "DELETE FROM pingpu WHERE path = '"+path+"'"
        mycursor.execute(sql)
        mydb.commit()
        print(mycursor.rowcount, " 条记录删除")


